/*
 ************************************************************************************
 * Copyright (C) 2001-2006 openERP S.L.
 * Licensed under the Apache Software License version 2.0
 * You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to  in writing,  software  distributed
 * under the License is distributed  on  an  "AS IS"  BASIS,  WITHOUT  WARRANTIES  OR
 * CONDITIONS OF ANY KIND, either  express  or  implied.  See  the  License  for  the
 * specific language governing permissions and limitations under the License.
 ************************************************************************************
*/
namespace org.openERP.data;

using java.util.StringTokenizer;
using java.util.Vector;
using java.util.Enumeration;
using java.util.Stack;
using java.util.Properties;
using java.sql.*;

using java.io.PrintWriter;
using java.io.IOException;
using java.io.FileReader;
using java.io.FileWriter;
using java.io.File;
using java.io.FileInputStream;
using java.io.OutputStreamWriter;
using java.io.FileOutputStream;

using org.xml.sax.Attributes;
using org.xml.sax.helpers.DefaultHandler;
using org.xml.sax.InputSource;
using org.xml.sax.XMLReader;
using org.xml.sax.SAXException;
using org.apache.xerces.parsers.*;

using org.apache.log4j.Logger;
using org.apache.log4j.PropertyConfigurator;


/**
 * Create a java file with functions based on sql sentences
 *
 **/
public class Sqlc extends DefaultHandler {
  public static final String VERSION = "V1.O00-1";
  String sqlcName;
  String sqlcnamespace = null;
  Stack<String> stcElement;   // Stack of Elements
  String strElement;
  String strDriver;
  String strURL;
  String strDBUser;
  String strDBPassword;
  String javaDateFormat;
  static String javaFileName;
  Connection connection;
  Sql sql;
  String strComments;
  PreparedStatement preparedStatement;
  ResultSet result;
  ResultSetMetaData rsmd;
  int numCols;
  boolean first = true;
  OutputStreamWriter out;
  PrintWriter printWriterTxt;
  Parameter parameterSql;
  boolean error;
  boolean writeTxtFiles = false;
  StringBuffer buffer;

  static Logger log4j = Logger.getLogger(Sqlc.class);  //log4j

  public Sqlc () {
    init();
  }

  public void init() {
    sql = new Sql();
    stcElement = new Stack<String>();
    first = true;
    sqlcnamespace = null;
  }

  public static void main(String argv[]) throws Exception {
    PropertyConfigurator.configure("log4j.lcf");
    String dirIni;
    String dirFin;
    boolean boolFilter;
    String strFilter;
    DirFilter dirFilter = null;

    if (argv.length < 1) {
      log4j.error("Usage: java org.openERP.data.Sqlc connection.xml [fileTermination [sourceDir destinyDir [write_txt_files]]]");
      return;
    }

    Sqlc sqlc = new Sqlc();
    XMLReader parser;
    parser = new SAXParser();
    if (argv.length <= 4) sqlc.writeTxtFiles = false;
    else sqlc.writeTxtFiles = argv[4].equalsIgnoreCase("true");
    parser.setContentHandler(sqlc);
    String strFileConnection = argv[0];
    sqlc.readProperties(strFileConnection.replace("/dbCon5.xml","/openERP.properties"));

    // the first parameter is the directory where the search is done
    if(argv.length <= 2)
      dirIni = ".";
    else
      dirIni = argv[2];

    if(argv.length <= 3)
      dirFin = dirIni;
    else
      dirFin = argv[3];

    // the second parameter is the string-chain to make the filter
    // the file must end with this string-chain in order to be recognized
    boolFilter = true; // there always must be a termination
    if(argv.length <= 1)
      strFilter = ".xml";
    else
      strFilter = argv[1];
    dirFilter = new  DirFilter(strFilter);
    log4j.info("directory source: " + dirIni);
    log4j.info("directory destiny: " + dirFin);
    log4j.info("file termination: " + strFilter);
    log4j.info("file connection: " + strFileConnection);
    log4j.info("Write TXT Files: " + sqlc.writeTxtFiles);
    try {
      //      parser.parse(strFileConnection);
      parser.parse(new InputSource(new FileReader(new File(strFileConnection))));
      try {
        sqlc.connect();
      } catch(ClassNotFoundException e){
        log4j.error("Class " + sqlc.strDriver + " not found: " + e);
        return;
      } catch(SQLException e){
        log4j.error("Error in URL: "+ sqlc.strURL + " of connection: " + e);
        return;
      }

    } catch (IOException e) {
      e.printStackTrace();
    } catch (SAXException e) {
      e.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    }

    File path = new File(dirIni);
    if (!path.exists()) {
      log4j.error("Directory does not exist: " + dirIni);
      return;
    }
    File fileFin = new File(dirFin);
    if (!fileFin.exists()) {
      log4j.error("Directory does not exist: " + dirFin);
      return;
    }
    listDir(path, boolFilter, dirFilter, sqlc, parser, strFilter, fileFin);

    sqlc.closeConnection();
  }

  // list of files and directories related to a File
  public static void listDir(File file, boolean boolFilter, DirFilter dirFilter,
      Sqlc sqlc, XMLReader parser, String strFilter, File fileFin) {
    File[] list;
    if(boolFilter)
      list = file.listFiles(dirFilter);
    else
      list = file.listFiles();
    for(int i = 0; i < list.length; i++) {
      File fileItem = list[i];
      if (fileItem.isDirectory()) {
        // if it is a subdirectory then list recursively
        listDir(fileItem, boolFilter, dirFilter, sqlc, parser, strFilter, fileFin);
      } else {
        try {
          if (log4j.isDebugEnabled()) log4j.debug(list[i] + " Parent: " + fileItem.getParent() + " getName() " + fileItem.getName() + " canonical: " + fileItem.getCanonicalPath());
          parseSqlFile(list[i], sqlc, parser, strFilter, fileFin);
        } catch (IOException e) {
          log4j.error("IOException: " + e);
        }
      }
    }
  }

  /* replace dirIni.toString() with file
     look for termination only at the end
     */

  private static void parseSqlFile(File fileParsing, Sqlc sqlc, XMLReader parser, String strFilter, File fileFin) {
    String strFileName = fileParsing.getName();
    if (log4j.isDebugEnabled()) log4j.debug("Parsing of " + strFileName);
    sqlc.init();
    if (log4j.isDebugEnabled()) log4j.debug("new Sql");
    int pos = strFileName.indexOf(strFilter);
    if (pos == -1) {
      log4j.error("File " + strFileName + " don't have termination " + strFilter);
      return;
    }
    String strFileWithoutTermination = strFileName.substring(0, pos);
    if (log4j.isDebugEnabled()) log4j.debug("File without termination: " + strFileWithoutTermination);
    if (strFileWithoutTermination.equalsIgnoreCase("SQLC")) {
      log4j.error("Name Sqlc not allowed for a file");
      return;
    }
    try {
      File dirJava = new File(fileFin, fileParsing.getParent());
      dirJava.mkdirs();
      javaFileName = TransformaNombreFichero(strFileWithoutTermination);
      File fileJava = new File(dirJava, javaFileName + ".java");
      File fileTxt = null;
      if (sqlc.writeTxtFiles) fileTxt = new File(fileParsing.getParent(), strFileWithoutTermination + ".txt");
      if ((!fileJava.exists())|| (fileParsing.lastModified() > fileJava.lastModified())) {
        if (log4j.isDebugEnabled()) log4j.debug(" time file parsed: " + fileParsing.lastModified() +
            " time file java: " + fileParsing.lastModified());
        FileOutputStream resultsFile = new FileOutputStream(fileJava);
        sqlc.out  = new OutputStreamWriter(resultsFile, "UTF-8");
        /*FileWriter resultsFile = new FileWriter(fileJava);
          sqlc.out = new PrintWriter(resultsFile);*/
        FileWriter resultsFileTxt = null;
        if (sqlc.writeTxtFiles) {
          resultsFileTxt = new FileWriter(fileTxt);
          sqlc.printWriterTxt = new PrintWriter(resultsFileTxt);
        }
        log4j.info("File: " + fileParsing + " \tprocessed");
        java.util.Date date = new java.util.Date();  // there is date in java.sql.*
        if (log4j.isDebugEnabled()) log4j.debug("Time: " + date.getTime()); 

        sqlc.error = false;
        try {
          parser.parse(new InputSource(new FileReader(fileParsing)));
        } catch (IOException e) {
          e.printStackTrace();
        } catch (SAXException e) {
          e.printStackTrace();
        } catch (Exception e) {
          e.printStackTrace();
        }
        if (!sqlc.first) {
          sqlc.printEndClass();
        }
        sqlc.out.flush();
        resultsFile.close();
        if (resultsFileTxt!=null) resultsFileTxt.close();
        if (sqlc.error) {
          fileJava.delete();
          fileTxt.delete();
        }
      } else {
        if (log4j.isDebugEnabled()) log4j.debug("File: " + fileParsing + " \tskipped");
      }
    } catch(IOException e) {
      e.printStackTrace();
      log4j.error("Problem at close of the file");
    }
  }

  private void pushElement(String name) {
    stcElement.push(name);
    strElement = name;
  }

  private void popElement() {
    strElement = (String)stcElement.pop();
    if (!stcElement.isEmpty()) strElement = (String)stcElement.peek();
  }

  public void startElement(java.lang.String uri,
      java.lang.String name,
      java.lang.String qName,
      Attributes amap) { //throws SAXException {
    readBuffer();
    pushElement(qName);
    if (log4j.isDebugEnabled()) log4j.debug("Configuration: startElement is called: element  name=" + name);
    if (log4j.isDebugEnabled()) log4j.debug("Configuration: startElement is called: element qName=" + qName);
    if (name.trim().equalsIgnoreCase("CONNECTION")) {
      int size = amap.getLength();
      for (int i = 0; i < size; i++) {     
        if (amap.getQName(i).equals("driver")) {
          strDriver = amap.getValue(i);
        } else if (amap.getQName(i).equals("URL")) {
          strURL = amap.getValue(i);
        } else if (amap.getQName(i).equals("DBUser")) {
          strDBUser = amap.getValue(i);
        } else if (amap.getQName(i).equals("DBPassword")) {
          strDBPassword = amap.getValue(i);
        }
      }
    } else if (name.equals("SqlMethod")) {
      sql.sqlStatic = "true";
      sql.sqlConnection = "false";
      String sqlnamespace = null;
      int size = amap.getLength();
      for (int i = 0; i < size; i++) {
        if (amap.getQName(i).equals("name")) {
          sql.sqlName = amap.getValue(i);
        } else if (amap.getQName(i).equals("return")) {
          sql.sqlReturn = amap.getValue(i).trim();
          if (sql.sqlReturn.equalsIgnoreCase("STRING") || 
              sql.sqlReturn.equalsIgnoreCase("BOOLEAN") || 
              sql.sqlReturn.equalsIgnoreCase("DATE") || 
              sql.sqlReturn.equalsIgnoreCase("SINGLE") || 
              sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) {
            sql.executeType = "executeQuery";
          } else if (sql.sqlReturn.equalsIgnoreCase("ROWCOUNT") || 
              sql.sqlReturn.equalsIgnoreCase("SEQUENCE")) {
            sql.executeType = "executeUpdate";
          } else if (sql.sqlReturn.equalsIgnoreCase("OBJECT")) {
            sql.executeType = "execute";
          } else {
            log4j.error("return not permited");
          }
        } else if (amap.getQName(i).equals("default")) {
          sql.sqlDefaultReturn = amap.getValue(i);
        } else if (amap.getQName(i).equals("static")) {
          sql.sqlStatic = amap.getValue(i);
        } else if (amap.getQName(i).equals("connection")) {  // it shows that the connection is sent through the window
          sql.sqlConnection = amap.getValue(i);
        } else if (amap.getQName(i).equals("type")) {
          sql.sqlType = amap.getValue(i);
        } else if (amap.getQName(i).equals("object")) {
          sql.sqlObject = amap.getValue(i);
        } else if (amap.getQName(i).equals("package")) {
          sqlnamespace = amap.getValue(i);
        } else if (amap.getQName(i).equals("import")) {
          sql.sqlusing = amap.getValue(i);
        }
      }
      if (sqlnamespace != null) sql.sqlClass = sqlnamespace + "." + sql.sqlObject;
      else sql.sqlClass = sql.sqlObject;
    } else if (name.equals("SqlClass")) {
      int size = amap.getLength();
      for (int i = 0; i < size; i++) {
        if (amap.getQName(i).equals("name")) {
          sqlcName = amap.getValue(i);
        } else if (amap.getQName(i).equals("package")) {
          sqlcnamespace = amap.getValue(i);
        }
      }
    } else if (name.equals("Parameter")) {
      String strName = null;
      String strDefault = null;
      String strInOut = "in";
      String strOptional = null;
      String strAfter = null;
      String strText = null;
      int size = amap.getLength();
      for (int i = 0; i < size; i++) {     
        if (amap.getQName(i).equals("name")) {
          strName = amap.getValue(i);
        } else if (amap.getQName(i).equals("default")) {
          strDefault = amap.getValue(i);
        } else if (amap.getQName(i).equals("type")) {
          strInOut = amap.getValue(i);
        } else if (amap.getQName(i).equals("optional")) {
          strOptional = amap.getValue(i);
        } else if (amap.getQName(i).equals("after")) {
          strAfter = amap.getValue(i);
        } else if (amap.getQName(i).equals("text")) {
          strText = amap.getValue(i);
        }
      }
      if (log4j.isDebugEnabled()) log4j.debug("Configuration: call to addParameter ");
      parameterSql = sql.addParameter(false, strName, strDefault, strInOut, strOptional, strAfter, strText);
    } else if (name.equals("Field")) {
      FieldAdded field = null;
      int size = amap.getLength();
      for (int i = 0; i < size; i++) {
        if (amap.getQName(i).equals("name")) {
          field = new FieldAdded(amap.getValue(i));
          sql.vecFieldAdded.addElement(field);
        } else if (amap.getQName(i).equals("value")) {
          field.strValue = amap.getValue(i);
        }
      }
    } else if (name.equals("Sequence")) {
      int size = amap.getLength();
      for (int i = 0; i < size; i++) {     
        if (amap.getQName(i).equals("name")) {
          sql.strSequenceName = amap.getValue(i);
        }
      }
      parameterSql = sql.addParameter(true, sql.strSequenceName, null, null, null, null, null);
    }
  }

  public void endElement(java.lang.String uri,
      java.lang.String name,
      java.lang.String qName) { //throws SAXException {
    readBuffer();
    if (log4j.isDebugEnabled()) log4j.debug("Configuration: call to endElement: " + name);
    if (log4j.isDebugEnabled()) log4j.debug("(before pop) Element: " + strElement);
    popElement();
    if (log4j.isDebugEnabled()) log4j.debug("(after pop) Element: " + strElement);
    if (name.equals("SqlMethod")) {
      if (sql.sqlType.equals("constant")) {
        try {
          printFunctionConstant();
        } catch (IOException ex) {
          ex.printStackTrace();
        }
      } else {
        query();
        if (first) {
          first = false;
          try {
            printInitClass();
          } catch (IOException ex) {
            ex.printStackTrace();
          }
        }
        try {
          printFunctionSql();
        } catch (IOException ex) {
          ex.printStackTrace();
        }
      }
      sql = new Sql();
    }
  }

  public void characters(char[] ch, int start, int lengthc) throws SAXException {
    if (buffer == null) buffer = new StringBuffer();
    buffer.append(ch, start, lengthc); 
  }
  public void readBuffer() {
    if (buffer != null) {
      String strBuffer = buffer.toString();
      if (log4j.isDebugEnabled()) log4j.debug("Configuration("+strElement+"): characters are  called: " + strBuffer);
      if (strElement.equals("Sql")) {
        sql.strSQL = strBuffer;
      } else if (strElement.equals("SqlClassComment")) {
        strComments = strBuffer;
      } else if (strElement.equals("SqlMethodComment")) {
        sql.strSqlComments = strBuffer;
      } else if (strElement.equals("Parameter")) {
        parameterSql.strText = strBuffer.replaceAll("\\x0D"," ").replaceAll("\\x0A"," ");
      }
      buffer = null;
    }
  }

  public void connect() throws ClassNotFoundException, SQLException {
    log4j.info("Loading driver: " + strDriver);
    Class.forName(strDriver);
    log4j.info("Driver loaded");
    if (strDBUser==null || strDBUser.equals("")) connection=DriverManager.getConnection(strURL);
    else connection=DriverManager.getConnection(strURL, strDBUser, strDBPassword);
    log4j.info("connect made");
  }

  public void closeConnection() {
    try {
      connection.close();
    } catch(SQLException e){
      log4j.error("SQL error in closeConnection: " + e);
    }
  }

  public void query(){
    try {
      if (preparedStatement!=null) preparedStatement.close();
      preparedStatement = connection.prepareStatement(sql.strSQL);
      if (log4j.isDebugEnabled()) log4j.debug("Prepared statement: " + sql.strSQL);
      /*    Commented because it is not a supported operation
            ResultSetMetaData rsmdPS = preparedStatement.getMetaData ();
      // Get the number of columns in the result set
      int numColsPS = rsmdPS.getColumnCount ();
      if (log4j.isDebugEnabled()) log4j.debug("number of columns in PS: " + numColsPS);
      */
      int i = 1;
      for (Enumeration e = sql.vecParameter.elements() ; e.hasMoreElements() ;) {
        Parameter parameter = (Parameter)e.nextElement();
        if (!parameter.boolOptional) {
          if (parameter.type == java.sql.Types.INTEGER) {
            if (parameter.strDefault == null) {
              if (log4j.isDebugEnabled()) log4j.debug("setInt: " + i + " value: 0");
              preparedStatement.setInt(i, 0);
            } else {
              if (log4j.isDebugEnabled()) log4j.debug("setInt: " + i + " value: " + parameter.strDefault);
              preparedStatement.setInt(i, Integer.parseInt(parameter.strDefault));
            }
            preparedStatement.setInt(i, Integer.parseInt(parameter.strDefault));
          } else if (parameter.type == java.sql.Types.VARCHAR) {
            if (parameter.strDefault == null) {
              if (log4j.isDebugEnabled()) log4j.debug("setString: " + i + " value: null");
              preparedStatement.setNull(i, java.sql.Types.VARCHAR);
            } else {
              if (log4j.isDebugEnabled()) log4j.debug("setString: " + i + " value: " + parameter.strDefault);
              preparedStatement.setString(i, parameter.strDefault);
            }
          }
          i++;
        }
      }
      // Get the ResultSetMetaData.  This will be used for
      // the column headings
      if (sql.executeType.equals("executeQuery")) {
        result = preparedStatement.executeQuery();
        if (log4j.isDebugEnabled()) log4j.debug("query done");
        rsmd = result.getMetaData ();
        // Get the number of columns in the result set
        numCols = rsmd.getColumnCount ();
        if (log4j.isDebugEnabled()) log4j.debug("number of columns: " + numCols);
      } //else
      //rsmd = preparedStatement.getMetaData ();
    } catch(SQLException e){
      error = true;
      log4j.error("SQL error in query: " + sql.strSQL + "Exception:"+ e);
      e.printStackTrace();
    } catch(Exception e){
      error = true;
      log4j.error("Error in query. Exception:"+ e);
      e.printStackTrace();
    }
  }

  public void printInitClass() throws IOException {
    if (this.writeTxtFiles) printTxtFile();
    out.write("//Sqlc generated " + VERSION + "\n"); // v0.011");
    if (sqlcnamespace != null) {
      out.write("namespace " + sqlcnamespace + ";\n");
    }
    out.write("\n");
    out.write("using java.sql.*;\n");
    out.write("using java.util.*;\n");
    out.write("\n");
    out.write("using org.apache.log4j.Logger;\n");
    out.write("\n");
    out.write("using javax.servlet.ServletException;\n");
    out.write("\n");
    out.write("using org.openERP.data.FieldProvider;\n");
    out.write("using org.openERP.database.ConnectionProvider;\n");
    out.write("using org.openERP.data.UtilSql;\n");
    out.write("using org.openERP.database.RDBMSIndependent;\n");
    out.write("using org.openERP.exception.*;\n");
    if (sql.sqlusing != null) {
      out.write("using " + sql.sqlusing + ";\n");
    }
    out.write("\n");

    String[] strCommentsVector = stringToVector(strComments, false);
    for (int i=0; i<strCommentsVector.length; i++) {
      if (i == 0) {
        out.write("/**\n" + strCommentsVector[i] + "\n");
      } else {
        out.write(" *" + strCommentsVector[i] + "\n");
      }
      if (i == strCommentsVector.length - 1) {
        out.write(" */\n");
      }
    }
    if (!javaFileName.equals(sqlcName)) throw new IOException("File name for xsql class " + javaFileName + " is different than the class name defined inside the file: " + sqlcName);
    out.write("public class " + sqlcName + " implements FieldProvider {\n");
    out.write("static Logger log4j = Logger.getLogger("+sqlcName+".class);\n");
    try {
      // Display column headings
      if (log4j.isDebugEnabled()) log4j.debug("Number of columns: "+ numCols);
      out.write("  private String InitRecordNumber=\"0\";\n");
      for (int i=1; i<=numCols; i++) {
        out.write("  public String ");
        out.write(TransformaNombreColumna(rsmd.getColumnLabel(i)));
        out.write(";\n");
      }
      for (Enumeration e = sql.vecFieldAdded.elements() ; e.hasMoreElements() ;) {
        FieldAdded fieldAdded = (FieldAdded)e.nextElement();
        out.write("  public String ");
        out.write(fieldAdded.strName);
        out.write(";\n");
      }
    } catch(SQLException e){
      log4j.error("SQL Exception error:"+ e);
    }
    out.write("\n");
    out.write("  public String getInitRecordNumber() {\n");
    out.write("    return InitRecordNumber;\n");
    out.write("  }\n");
    // the getField function
    out.write("\n");
    out.write("  public String getField(String fieldName) {\n");
    try {
      // Display column headings
      if (log4j.isDebugEnabled()) log4j.debug("Number of columns in getField: "+ numCols);
      for (int i=1; i<=numCols; i++) {
        String columnLabel = rsmd.getColumnLabel(i);
        String transformedColumnLabel = TransformaNombreColumna(columnLabel);
        if (i == 1) {
          out.write("    if ");
        } else {
          out.write("    else if ");
        }
        out.write("(fieldName.equalsIgnoreCase(\"");
        out.write(columnLabel);
        if (!columnLabel.equalsIgnoreCase(transformedColumnLabel))
          out.write("\") || fieldName.equals(\"" + transformedColumnLabel);
        out.write("\"))\n");
        out.write("      return " + transformedColumnLabel + ";\n");
      }
      for (Enumeration e = sql.vecFieldAdded.elements() ; e.hasMoreElements() ;) {
        FieldAdded fieldAdded = (FieldAdded)e.nextElement();
        out.write("    else if ");
        out.write("(fieldName.equals(\"");
        out.write(fieldAdded.strName + "\"))\n");
        out.write("      return " + fieldAdded.strName + ";\n");
      }
    } catch(SQLException e){
      log4j.error("SQL Exception error:"+ e);
    }
    out.write("   else {\n");
    out.write("     log4j.debug(\"Field does not exist: \" + fieldName);\n");
    out.write("     return null;\n");
    out.write("   }\n");
    out.write(" }\n");
  }

  void printTxtFile() {
    try {
      // Display column headings
      if (log4j.isDebugEnabled()) log4j.debug("Printing txt File: "+ numCols);
      for (int i=1; i<=numCols; i++) {
        printWriterTxt.print(rsmd.getColumnLabel(i));
        if (i == numCols) {
          printWriterTxt.println("");
        } else {
          printWriterTxt.print(", ");
        }
      }
      printWriterTxt.println("");
      for (int i=1; i<=numCols; i++) {
        printWriterTxt.println("<FIELD id=\"" +
            TransformaNombreColumna("field_" + rsmd.getColumnLabel(i)) + "\">" +
            TransformaNombreColumna(rsmd.getColumnLabel(i)) + "</FIELD>");
      }
      printWriterTxt.println("");
      for (int i=1; i<=numCols; i++) {
        printWriterTxt.println("      <Parameter name=\"" + 
            TransformaNombreColumna(rsmd.getColumnLabel(i)) + "\"/>");
      }
      printWriterTxt.println("");
      printWriterTxt.println("  " + sqlcName + " getEditVariables(VariablesSapp vars) {");
      printWriterTxt.println("    " + sqlcName + " data = new " + sqlcName + "();");
      for (int i=1; i<=numCols; i++) {
        printWriterTxt.println("    data." +
            TransformaNombreColumna(rsmd.getColumnLabel(i)) +
            " = vars.getStringParameter(\"" +
            TransformaNombreColumna("inp_" + rsmd.getColumnLabel(i)) + "\");");
      }
      printWriterTxt.println("    data.a1Usuario = vars.getUser();");
      printWriterTxt.println("    data.a1Rol = vars.getRole();");
      printWriterTxt.println("    return data;");
      printWriterTxt.println("  }");
    } catch(SQLException e){
      log4j.error("SQL Exception error:"+ e);
    }
  }

  public void printFunctionConstant() throws IOException {
    printHeadFunctionSql(false, false, false);
    out.write("    " + sqlcName + " object" + sqlcName + "[] = new " + sqlcName + "[1];\n");
    out.write("    object" + sqlcName + "[0] = new " + sqlcName + "();\n");
    try {
      for (int i=1; i<=numCols; i++) {
        String strNameLabel = rsmd.getColumnLabel(i);
        out.write("    object" + sqlcName + "[0]." +
            TransformaNombreColumna(strNameLabel) + " = ");
        boolean printedParameter = false;
        for (Enumeration e = sql.vecParameter.elements() ; e.hasMoreElements() ;) {
          Parameter parameter = (Parameter)e.nextElement();
          if (parameter.strName.equals(TransformaNombreColumna(strNameLabel)) && !printedParameter) {
            out.write(TransformaNombreColumna(strNameLabel) + ";\n");
            printedParameter = true;
          }
        }
        if (!printedParameter) {
          out.write("\"\";\n");
        }
      }
    } catch(SQLException e){
      log4j.error("SQL Exception error:"+ e);
    }
    out.write("    return object" + sqlcName + ";\n");
    out.write("  }\n");
  }

  public void printSQLBody() throws IOException {
    // codigo para imprimir trozos de Sql opcionales
    int posSQL = 0;
    out.write("    String strSql = \"\";\n");
    for (Enumeration e = sql.vecParameter.elements() ; e.hasMoreElements() ;) {
      Parameter parameter = (Parameter)e.nextElement();
      if (parameter.boolOptional){
        if (parameter.strAfter == null) {
          parameter.strAfter = "WHERE";
          parameter.strText = parameter.strName + " = ? AND";
        }
        if (parameter.strName != null && // se deben imprimir los repetidos, quitar: !parameter.boolRepeated &&
            !parameter.strInOut.equals("out")) {
          int posFinalAfter = posFinal(sql.strSQL, parameter.strAfter);
          if (posFinalAfter != -1) {
            imprimirSubstring(sql.strSQL, posSQL, posFinalAfter, out);
            posSQL = posFinalAfter + (parameter.strInOut.equals("replace")?parameter.strText.length():0);
            if (parameter.strInOut.equals("none")) {
              out.write("    strSql = strSql + ((" +
                  parameter.strName + ".equals(\"" + parameter.strName + "\"))?\" " +
                  parameter.strText + " \":\"\");\n");
            } else if (parameter.strInOut.equals("argument")) {
              out.write("    strSql = strSql + ((" + parameter.strName + "==null || " +
                  parameter.strName + ".equals(\"\"))?\"\":\" " +
                  parameter.strText + "\" + " + parameter.strName + ");\n");
            } else if (parameter.strInOut.equals("replace")) {
              out.write("    strSql = strSql + ((" + parameter.strName + "==null || " +
                  parameter.strName + ".equals(\"\"))?\"\":" + parameter.strName + ");\n");
            } else {
              out.write("    strSql = strSql + ((" + parameter.strName + "==null || " +
                  parameter.strName + ".equals(\"\"))?\"\":\" " +
                  parameter.strText + " \");\n");
            }
          } else if (parameter.strInOut.equals("replace")) {
            posFinalAfter=0;
            out.write("    strSql = strSql + ((" + parameter.strName + "==null || " +
                parameter.strName + ".equals(\"\"))?\"\":" + parameter.strName + ");\n");
          }
            }
      }
    }
    imprimirSubstring(sql.strSQL, posSQL, sql.strSQL.length(), out);

    out.write("\n");

    if (sql.executeType.equals("executeQuery")) {
      out.write("    ResultSet result;\n");
    }
    if (sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) {
      out.write("    Vector<Object> vector = new Vector<Object>(0);\n");
    } else if (sql.sqlReturn.equalsIgnoreCase("SINGLE")) {
      out.write("    " + sqlcName + " object" + sqlcName + " = new " + sqlcName + "();\n");
    } else if (sql.sqlReturn.equalsIgnoreCase("STRING")) {
      out.write("    String strReturn = ");
      if (sql.sqlDefaultReturn != null) {
        out.write("\"" + sql.sqlDefaultReturn + "\";\n");
      } else {
        out.write("null;\n");  // if the return value is not specified then set to null
      }
    } else if (sql.sqlReturn.equalsIgnoreCase("BOOLEAN")) {
      out.write("    boolean boolReturn = ");
      if (sql.sqlDefaultReturn != null) {
        out.write(sql.sqlDefaultReturn + ";\n");
      } else {
        out.write("false;\n");  // if the return value is not specified then set to false
      }
    } else if (sql.sqlReturn.equalsIgnoreCase("DATE")) {
      out.write("    String dateReturn = ");
      if (sql.sqlDefaultReturn != null) {
        out.write(sql.sqlDefaultReturn + ";\n");
      } else {
        out.write("null;\n");  // if the return value is not specified then set to null
      }
    } else if (sql.sqlReturn.equalsIgnoreCase("OBJECT")) {
      //      out.write("    SqlRespuestaCS objectSqlRespuestaCS = new SqlRespuestaCS();");
      out.write("    " + sql.sqlClass + " object" + sql.sqlObject + " = new " + sql.sqlClass + "();\n");
    }
    if (sql.executeType.equals("executeUpdate")) {
      out.write("    int updateCount = 0;\n");
    }

    if (sql.sqlType.equals("preparedStatement")) {
      out.write("    PreparedStatement st = null;\n");
    } else if (sql.sqlType.equals("statement")) {
      out.write("    Statement st = null;\n");
    } else if (sql.sqlType.equals("callableStatement")) {
      out.write("    CallableStatement st = null;\n");
      out.write("    if (connectionProvider.getRDBMS().equalsIgnoreCase(\"ORACLE\")) {\n");
    }
    out.write("\n");
  }

  public void printSQLParameters() throws IOException {
    out.write("    int iParameter = 0;\n");
    out.write("    try {\n");
    if (sql.sqlType.equals("preparedStatement")) {
      out.write("    st = connectionProvider.getPreparedStatement(");
      if (sql.sqlConnection.equals("true")) out.write("conn, ");
      out.write("strSql);\n");
    } else if (sql.sqlType.equals("statement")) {
      out.write("    st = connectionProvider.getStatement(");
      if (sql.sqlConnection.equals("true")) out.write("conn");
      out.write(");\n");
    } else if (sql.sqlType.equals("callableStatement")) {
      out.write("      st = connectionProvider.getCallableStatement(");
      if (sql.sqlConnection.equals("true")) out.write("conn, ");
      out.write("strSql);\n");
    }
    // set value of parameters
    for (Enumeration e = sql.vecParameter.elements() ; e.hasMoreElements() ;) {
      Parameter parameter = (Parameter)e.nextElement();
      if (parameter.boolSequence){
        out.write("      iParameter++; st.setLong(iParameter, keySequence);\n");
      } else if (parameter.strName != null) {
        if (parameter.strInOut.equals("out") || parameter.strInOut.equals("inOut")) {
          out.write("      int iParameter" + parameter.strName + " = iParameter + 1;\n");
        }
        if (parameter.boolOptional) {
          out.write("      if (" + parameter.strName + " != null && !(" + parameter.strName + ".equals(\"\"))) {\n");
          out.write("  ");
        }
        if (parameter.strInOut.equals("in") || parameter.strInOut.equals("inOut")) {
          out.write("      iParameter++; UtilSql.setValue(st, iParameter, " + parameter.type + ", ");
          if (parameter.strDefault == null) {
            out.write("null, ");
          } else {
            out.write("\"" + parameter.strDefault + "\", ");
          }
          out.write(parameter.strName + ");\n");
        }
        if (parameter.strInOut.equals("out")) {
          out.write("      iParameter++; st.registerOutParameter(iParameter, " + parameter.type+ ");\n");
        } else if (parameter.strInOut.equals("inOut")) {   // in this case iParamter is increased;
          out.write("      st.registerOutParameter(iParameter, " + parameter.type+ ");\n");
        }
        if (parameter.boolOptional) {
          out.write("      }\n");
        }
      }
    }

    out.write("\n");
  }

  public void printKeySequenceSql() throws IOException {
    printHeadFunctionSql(true, false, true);
    out.write("    boolean existsKey = false;\n");
    printSQLBody();
    printSQLParameters();

    out.write("      result = st." + sql.executeType + "(");
    if (sql.sqlType.equals("statement")) out.write("strSql");
    out.write(");\n");
    out.write("      long countRecord = 0;\n");
    out.write("      long initRecord = 0;\n");
    out.write("      boolean searchComplete = false;\n");
    out.write("      while(result.next() && !searchComplete) {\n");
    out.write("        countRecord++;\n");
    out.write("        " + sqlcName + " object" + sqlcName + " = new " + sqlcName + "();\n");
    try {
      for (int i=1; i<=numCols; i++) {
        if (log4j.isDebugEnabled()) log4j.debug("Columna: " + rsmd.getColumnName(i) + " tipo: " + rsmd.getColumnType(i));
        if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP || rsmd.getColumnType(i) == 91 ) {
          out.write("        object" + sqlcName + "." + TransformaNombreColumna(rsmd.getColumnLabel(i)) + " = UtilSql.getDateValue(result, \"" + rsmd.getColumnLabel(i) +"\", \""+javaDateFormat+"\");\n");
        } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
          out.write("        object" + sqlcName + "." + TransformaNombreColumna(rsmd.getColumnLabel(i)) + " = UtilSql.getBlobValue(result, \"" + rsmd.getColumnLabel(i) +"\");\n");
        } else {
          out.write("        object" + sqlcName + "." + TransformaNombreColumna(rsmd.getColumnLabel(i)) + " = UtilSql.getValue(result, \"" + rsmd.getColumnLabel(i) +"\");\n");
        }
      }
      for (Enumeration e = sql.vecFieldAdded.elements() ; e.hasMoreElements() ;) {
        FieldAdded fieldAdded = (FieldAdded)e.nextElement();
        if (fieldAdded.strValue.equals("count"))
          out.write("        object" + sqlcName + "." + fieldAdded.strName + " = Long.toString(countRecord);\n");
        else if (fieldAdded.strValue.equals("void"))
          out.write("        object" + sqlcName + "." + fieldAdded.strName + " = \"\";\n");
      }
      out.write("        object" + sqlcName + ".InitRecordNumber = Long.toString(initRecord);\n");
    } catch(SQLException e){
      log4j.error("SQL Exception error:"+ e);
      e.printStackTrace();
    }
    out.write("        if (!existsKey) existsKey = (object" + sqlcName + ".getField(keyName).equalsIgnoreCase(keyValue));\n");
    out.write("        vector.addElement(object" + sqlcName + ");\n");
    out.write("        if (countRecord == numberRegisters) {\n");
    out.write("          if (existsKey) searchComplete=true;\n");
    out.write("          else {\n");
    out.write("            countRecord = 0;\n");
    out.write("            initRecord += numberRegisters;\n");
    out.write("            vector.clear();\n");
    out.write("          }\n");
    out.write("        }\n");
    out.write("      }\n");


    out.write("      result.close();\n");
    out.write("    } catch(SQLException e){\n");
    out.write("      log4j.error(\"SQL error in query: \" + strSql + \"Exception:\"+ e);\n");
    out.write("      throw new ServletException(\"@CODE=\" + Integer.toString(e.getErrorCode()) + \"@\" + e.getMessage());\n");
    out.write("    } catch(Exception ex){\n");
    out.write("      log4j.error(\"Exception in query: \" + strSql + \"Exception:\"+ ex);\n");
    out.write("      throw new ServletException(\"@CODE=@\" + ex.getMessage());\n");
    out.write("    } finally {\n");
    out.write("      try {\n");
    if (!sql.sqlConnection.equals("true")) {
      if (sql.sqlType.equals("statement")) out.write("        connectionProvider.releaseStatement(st);\n");
      else out.write("        connectionProvider.releasePreparedStatement(st);\n");
    } else {
      if (sql.sqlType.equals("statement")) out.write("        connectionProvider.releaseTransactionalStatement(st);\n");
      else if (sql.sqlType.equalsIgnoreCase("preparedstatement")) out.write("        connectionProvider.releaseTransactionalPreparedStatement(st);\n");
    }
    out.write("      } catch(Exception ignore){\n");
    out.write("        ignore.printStackTrace();\n");
    out.write("      }\n");
    out.write("    }\n");
    if (sql.sqlType.equals("callableStatement")) out.write("    }\n");

    out.write("    if (existsKey) {\n");
    out.write("      " + sqlcName + " object" + sqlcName + "[] = new " + sqlcName + "[vector.size()];\n");
    out.write("      vector.copyInto(object" + sqlcName + ");\n");
    out.write("      return(object" + sqlcName + ");\n");
    out.write("    }\n");
    out.write("    return(new " + sqlcName + "[0]);\n");
    out.write("  }\n");
  }

  public void printFunctionSql() throws IOException {
    boolean boolSequence = false;
    //*** Call to the argument-less creation header, who calls the header with them 
    if (sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) {  // parameters por paging the output  && 1==2 
      printHeadFunctionSql(true, false, false);
      out.write("    return ");
      printCallFunctionSql(true);
      out.write(";\n");
      out.write("  }\n");
      boolSequence = true;
      printKeySequenceSql();
    }

    printHeadFunctionSql(true, boolSequence, false);
    // Sequences
    if (sql.strSequenceName != null) {
      out.write("    long keySequence = 0;\n");
      out.write("    String strSql1 = \"SELECT " + sql.strSequenceName);
      out.write(".NEXTVAL AS KEY_" + sql.strSequenceName.replace(".", "_") + " FROM DUAL\";\n");
      out.write("    PreparedStatement psl = null;\n");
      out.write("    try {\n");
      out.write("      psl = connectionProvider.getPreparedStatement(");
      if (sql.sqlConnection.equals("true")) out.write("conn, ");
      out.write("strSql1);\n");
      out.write("      ResultSet resultKey;\n");
      out.write("      resultKey = psl.executeQuery();\n");
      out.write("      if(resultKey.next()){\n");
      out.write("        keySequence = resultKey.getLong(\"KEY_" + sql.strSequenceName.replace(".", "_") + "\");\n");
      out.write("      }\n");
      out.write("      resultKey.close();\n");
      out.write("    } catch(SQLException e){\n");
      out.write("      log4j.error(\"SQL error in query: \" + strSql1 + \"Exception:\"+ e);\n");
      out.write("      throw new ServletException(\"@CODE=\" + Integer.toString(e.getErrorCode()) + \"@\" + e.getMessage());\n");
      out.write("    } catch(NoConnectionAvailableException ec){\n");
      out.write("      log4j.error(\"Connection error in query: \" + strSql1 + \"Exception:\"+ ec);\n");
      out.write("      throw new ServletException(\"@CODE=NoConnectionAvailable\");\n");
      out.write("    } catch(PoolNotFoundException ep){\n");
      out.write("      log4j.error(\"Pool error in query: \" + strSql1 + \"Exception:\"+ ep);\n");
      out.write("      throw new ServletException(\"@CODE=NoConnectionAvailable\");\n");
      out.write("    } catch(Exception ex){\n");
      out.write("      log4j.error(\"Exception in query: \" + strSql1 + \"Exception:\"+ ex);\n");
      out.write("      throw new ServletException(\"@CODE=@\" + ex.getMessage());\n");
      out.write("    } finally {\n");
      out.write("      try {\n");
      if (!sql.sqlConnection.equals("true")) {
        if (sql.sqlType.equals("statement")) out.write("        connectionProvider.releaseStatement(psl);\n");
        else out.write("        connectionProvider.releasePreparedStatement(psl);\n");
      } else {
        if (sql.sqlType.equals("statement")) out.write("       connectionProvider.releaseTransactionalStatement(psl);\n");
        else if (sql.sqlType.equalsIgnoreCase("preparedstatement")) out.write("        connectionProvider.releaseTransactionalPreparedStatement(psl);\n");
      }
      out.write("      } catch(Exception ignore){\n");
      out.write("        ignore.printStackTrace();\n");
      out.write("      }\n");
      out.write("    }\n");
      out.write("\n");
    }

    printSQLBody();
    printSQLParameters();

    if (sql.executeType.equals("executeQuery")) {
      out.write("      result = st." + sql.executeType + "(");
      if (sql.sqlType.equals("statement")) out.write("strSql");
      out.write(");\n");
      if (sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) {
        out.write("      long countRecord = 0;\n");
        //paging
        out.write("      long countRecordSkip = 1;\n");
        out.write("      boolean continueResult = true;\n");
        out.write("      while(countRecordSkip < firstRegister && continueResult) {\n");
        out.write("        continueResult = result.next();\n");
        out.write("        countRecordSkip++;\n");
        out.write("      }\n");
        //paging
        out.write("      while(continueResult && result.next()) {\n");
        out.write("        countRecord++;\n");
        out.write("        " + sqlcName + " object" + sqlcName + " = new " + sqlcName + "();\n");
      } else {
        out.write("      if(result.next()) {\n");
      }
      try {
        if (sql.sqlReturn.equalsIgnoreCase("STRING")) {
          out.write("        strReturn = UtilSql.getValue(result, \"" + rsmd.getColumnLabel(1) +"\");\n");
        } else if (sql.sqlReturn.equalsIgnoreCase("BOOLEAN")) {
          out.write("        boolReturn = result.getBoolean(\"" + rsmd.getColumnLabel(1) +"\");\n");
        } else if (sql.sqlReturn.equalsIgnoreCase("DATE")) {
          out.write("        dateReturn = UtilSql.getDateValue(result, \"" + rsmd.getColumnLabel(1) +"\", \""+javaDateFormat+"\");\n");
        } else {
          for (int i=1; i<=numCols; i++) {
            if (log4j.isDebugEnabled()) log4j.debug("Columna: " + rsmd.getColumnName(i) + " tipo: " + rsmd.getColumnType(i));
            if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP || rsmd.getColumnType(i) == 91 ) {
              out.write("        object" + sqlcName + "." + TransformaNombreColumna(rsmd.getColumnLabel(i)) + " = UtilSql.getDateValue(result, \"" + rsmd.getColumnLabel(i) +"\", \""+javaDateFormat+"\");\n");
            } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
              out.write("        object" + sqlcName + "." + TransformaNombreColumna(rsmd.getColumnLabel(i)) + " = UtilSql.getBlobValue(result, \"" + rsmd.getColumnLabel(i) +"\");\n");
            } else {
              out.write("        object" + sqlcName + "." + TransformaNombreColumna(rsmd.getColumnLabel(i)) + " = UtilSql.getValue(result, \"" + rsmd.getColumnLabel(i) +"\");\n");
            }
          }
          for (Enumeration e = sql.vecFieldAdded.elements() ; e.hasMoreElements() ;) {
            FieldAdded fieldAdded = (FieldAdded)e.nextElement();
            if (fieldAdded.strValue.equals("count"))
              out.write("        object" + sqlcName + "." + fieldAdded.strName + " = Long.toString(countRecord);\n");
            else if (fieldAdded.strValue.equals("void"))
              out.write("        object" + sqlcName + "." + fieldAdded.strName + " = \"\";\n");
          }
          out.write("        object" + sqlcName + ".InitRecordNumber = Integer.toString(firstRegister);\n");
        }
      } catch(SQLException e){
        log4j.error("SQL Exception error:"+ e);
        e.printStackTrace();
      }
      if (sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) {
        out.write("        vector.addElement(object" + sqlcName + ");\n");
        //paging
        out.write("        if (countRecord >= numberRegisters && numberRegisters != 0) {\n");
        out.write("          continueResult = false;\n");
        out.write("        }\n");
        //paging
      }
      out.write("      }\n");
      out.write("      result.close();\n");
    } else if (sql.executeType.equals("executeUpdate")) {
      out.write("      updateCount = st." + sql.executeType + "(");
      if (sql.sqlType.equals("statement")) out.write("strSql");
      out.write(");\n");
    } else if (sql.executeType.equals("execute")) {
      out.write("      st." + sql.executeType + "();\n");
      if (sql.sqlReturn.equalsIgnoreCase("OBJECT")) {
        for (Enumeration e = sql.vecParameter.elements() ; e.hasMoreElements() ;) {
          Parameter parameter = (Parameter)e.nextElement();
          if (!parameter.boolSequence && parameter.strName != null) {
            if (parameter.strInOut.equals("out") || parameter.strInOut.equals("inOut")) {
              out.write("      object" + sql.sqlObject + "." + parameter.strName);
              //              out.write(" = st.getString(" + jParameter + ");"); //", " + parameter.type+ ");");
              //              out.write(" = st.getString( iParameter" + parameter.strName + ");"); //", " + parameter.type+ ");");
              out.write("= UtilSql.getStringCallableStatement(st, iParameter" + parameter.strName + ");\n");
            }
          }
        }
      }
    }
    out.write("    } catch(SQLException e){\n");
    out.write("      log4j.error(\"SQL error in query: \" + strSql + \"Exception:\"+ e);\n");
    out.write("      throw new ServletException(\"@CODE=\" + Integer.toString(e.getErrorCode()) + \"@\" + e.getMessage());\n");
    out.write("    } catch(Exception ex){\n");
    out.write("      log4j.error(\"Exception in query: \" + strSql + \"Exception:\"+ ex);\n");
    out.write("      throw new ServletException(\"@CODE=@\" + ex.getMessage());\n");
    out.write("    } finally {\n");
    out.write("      try {\n");
    if (!sql.sqlConnection.equals("true")) {
      if (sql.sqlType.equals("statement")) out.write("        connectionProvider.releaseStatement(st);\n");
      else out.write("        connectionProvider.releasePreparedStatement(st);\n");
    } else {
      if (sql.sqlType.equals("statement")) out.write("        connectionProvider.releaseTransactionalStatement(st);\n");
      else if (sql.sqlType.equalsIgnoreCase("preparedstatement")) out.write("        connectionProvider.releaseTransactionalPreparedStatement(st);\n");
    }
    out.write("      } catch(Exception ignore){\n");
    out.write("        ignore.printStackTrace();\n");
    out.write("      }\n");
    out.write("    }\n");
    if (sql.sqlType.equals("callableStatement")) {
      out.write("    }\n");
      out.write("    else {\n");
      out.write("      Vector<String> parametersData = new Vector<String>();\n");
      out.write("      Vector<String> parametersTypes = new Vector<String>();\n");
      int outParams = 0;
      String outParamName = "";
      StringBuffer paramsReceipt = new StringBuffer();
      for (Enumeration eparams = sql.vecParameter.elements() ; eparams.hasMoreElements() ;) {
        Parameter parameter = (Parameter)eparams.nextElement();
        if (!parameter.strInOut.equals("none") && !parameter.strInOut.equals("argument") && !parameter.strInOut.equals("replace")) {
          out.write("      parametersData.addElement(" + (parameter.strInOut.equalsIgnoreCase("out")?"\"" + parameter.strName + "\"":parameter.strName) + ");\n");
          out.write("      parametersTypes.addElement(\"" + parameter.strInOut + "\");\n");
          if (parameter.strInOut.equals("out")) {
            outParamName = parameter.strName;
            paramsReceipt.append("      object").append(sql.sqlObject).append(".").append(outParamName).append(" = (String) vecTotal.elementAt(").append(outParams).append(");\n");
            outParams++;
          }
        }
      }
      out.write("      Vector<String> vecTotal = new Vector<String>();\n");
      out.write("      try {\n");
      if (outParams>0) out.write("        vecTotal = ");
      out.write("      RDBMSIndependent.getCallableResult(" + (sql.sqlConnection.equals("true")?"conn":"null") + ", connectionProvider, strSql, parametersData, parametersTypes, " + outParams + ");\n");
      if (outParams>0) out.write(paramsReceipt.toString());
      out.write("      } catch(SQLException e){\n");
      out.write("        log4j.error(\"SQL error in query: \" + strSql + \"Exception:\"+ e);\n");
      out.write("        throw new ServletException(\"@CODE=\" + Integer.toString(e.getErrorCode()) + \"@\" + e.getMessage());\n");
      out.write("      } catch(NoConnectionAvailableException ec){\n");
      out.write("        log4j.error(\"Connection error in query: \" + strSql + \"Exception:\"+ ec);\n");
      out.write("        throw new ServletException(\"@CODE=NoConnectionAvailable\");\n");
      out.write("      } catch(PoolNotFoundException ep){\n");
      out.write("        log4j.error(\"Pool error in query: \" + strSql + \"Exception:\"+ ep);\n");
      out.write("        throw new ServletException(\"@CODE=NoConnectionAvailable\");\n");
      out.write("      } catch(Exception ex){\n");
      out.write("        log4j.error(\"Exception in query: \" + strSql + \"Exception:\"+ ex);\n");
      out.write("        throw new ServletException(\"@CODE=@\" + ex.getMessage());\n");
      out.write("      }\n");
      out.write("    }\n");
    }

    if (sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) {
      out.write("    " + sqlcName + " object" + sqlcName + "[] = new " + sqlcName + "[vector.size()];\n");
      out.write("    vector.copyInto(object" + sqlcName + ");\n");
    }

    if (sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) {
      out.write("    return(object" + sqlcName + ");\n");
    } else if (sql.sqlReturn.equalsIgnoreCase("SINGLE")) {
      out.write("    return(object" + sqlcName + ");\n");
    } else if (sql.sqlReturn.equalsIgnoreCase("STRING")) {
      out.write("    return(strReturn);\n");
    } else if (sql.sqlReturn.equalsIgnoreCase("BOOLEAN")) {
      out.write("    return(boolReturn);\n");
    } else if (sql.sqlReturn.equalsIgnoreCase("DATE")) {
      out.write("    return(dateReturn);\n");
    } else if (sql.sqlReturn.equalsIgnoreCase("ROWCOUNT")) {
      out.write("    return(updateCount);\n");
    } else if (sql.sqlReturn.equalsIgnoreCase("SEQUENCE")) {
      out.write("    return(Long.toString(keySequence));\n");
    } else if (sql.sqlReturn.equalsIgnoreCase("OBJECT")) {
      out.write("    return(object" + sql.sqlObject + ");\n");
    }
    out.write("  }\n");
  }

  public void printHeadFunctionSql(boolean printProviderConnection, boolean boolPagin, boolean boolSequence) throws IOException {
    out.write("\n");
    String[] strSqlCommentsVector = stringToVector(sql.strSqlComments, false);
    for (int i=0; i<strSqlCommentsVector.length; i++) {
      if (i == 0) {
        out.write("/**\n" + strSqlCommentsVector[i] + "\n");
      } else {
        out.write(" *" + strSqlCommentsVector[i] + "\n");
      }
      if (i == strSqlCommentsVector.length - 1) {
        out.write(" */\n");
      }
    }
    out.write("  public ");
    if (sql.sqlStatic.equals("true")) {
      out.write("static ");
    }
    if (sql.sqlReturn.equalsIgnoreCase("MULTIPLE")) {
      out.write(sqlcName + "[] ");
    } else if (sql.sqlReturn.equalsIgnoreCase("SINGLE")) {
      out.write(sqlcName + " ");
    } else if (sql.sqlReturn.equalsIgnoreCase("STRING")) {
      out.write("String ");
    } else if (sql.sqlReturn.equalsIgnoreCase("BOOLEAN")) {
      out.write("boolean ");
    } else if (sql.sqlReturn.equalsIgnoreCase("DATE")) {
      out.write("String ");
    } else if (sql.sqlReturn.equalsIgnoreCase("SEQUENCE")) {
      out.write("String ");
    } else if (sql.sqlReturn.equalsIgnoreCase("ROWCOUNT")) {
      out.write("int ");
    } else if (sql.sqlReturn.equalsIgnoreCase("OBJECT")) {
      out.write(sql.sqlClass + " ");
    }
    out.write(sql.sqlName + "(");
    boolean firstParameter = true;
    if (sql.sqlConnection.equals("true")) {
      firstParameter = false;
      out.write("Connection conn");
    }
    if (printProviderConnection) {
      if (firstParameter) {
        firstParameter = false;
      } else {
        out.write(", ");
      }
      out.write("ConnectionProvider connectionProvider");
    }
    if (log4j.isDebugEnabled()) log4j.debug("Parameters numbering");
    for (Enumeration e = sql.vecParameter.elements() ; e.hasMoreElements() ;) {
      Parameter parameter = (Parameter)e.nextElement();
      if (sql.sqlStatic.equals("true")) {
        if (parameter.strName != null && !parameter.boolRepeated && !parameter.boolSequence && !parameter.strInOut.equals("out")) {
          if (firstParameter) {
            firstParameter = false;
          } else {
            out.write(", ");
          }
          out.write("String " + parameter.strName);
        }
      }
    }
    if (boolPagin) {  // parameters por paging the output
      if (firstParameter) {
        firstParameter = false;
      } else {
        out.write(", ");
      }
      out.write("int firstRegister, int numberRegisters");
    } // parameters por paging the output

    if (boolSequence) {
      if (firstParameter) {
        firstParameter = false;
      } else {
        out.write(", ");
      }
      out.write("String keyValue, String keyName, int numberRegisters");
    }
    out.write(")");
    out.write("    throws ServletException {\n");
  }

  public void printCallFunctionSql(boolean printProviderConnection) throws IOException {
    out.write(sql.sqlName + "(");
    boolean firstParameter = true;
    if (sql.sqlConnection.equals("true")) {
      firstParameter = false;
      out.write("conn");
    }
    if (printProviderConnection) {
      if (firstParameter) {
        firstParameter = false;
      } else {
        out.write(", ");
      }
      out.write("connectionProvider");
    }
    if (log4j.isDebugEnabled()) log4j.debug("Parameters numbering");
    for (Enumeration e = sql.vecParameter.elements() ; e.hasMoreElements() ;) {
      Parameter parameter = (Parameter)e.nextElement();
      if (sql.sqlStatic.equals("true")) {
        if (parameter.strName != null && !parameter.boolRepeated && !parameter.boolSequence && !parameter.strInOut.equals("out")) {
          if (firstParameter) {
            firstParameter = false;
          } else {
            out.write(", ");
          }
          out.write(parameter.strName);
        }
      }
    }
    if (firstParameter) {
      firstParameter = false;
    } else {
      out.write(", ");
    }
    out.write("0, 0");
    out.write(")");
  }

  public void printEndClass() throws IOException {
    out.write("}\n");
  }

  public int posFinal(String strSQL, String strPattern) {
    int index = sql.strSQL.indexOf(strPattern);
    if (index != -1) index = index + strPattern.length();
    return index;
  }

  public void imprimirSubstring(String strSQL, int posIni, int posFin, OutputStreamWriter out) throws IOException {
    String[] strSqlVector = stringToVector(strSQL.substring(posIni, posFin), true);
    for (int i=0; i<strSqlVector.length; i++) {
      if (i == 0) {
        out.write("    strSql = strSql + \n");
      }
      if (i<strSqlVector.length-1) {
        out.write("      \"" + strSqlVector[i] + "\" +\n");
      } else {
        out.write("      \"" + strSqlVector[i] + "\";\n");
      }
    }
  }

  /**
   * Convert a string with the character 0A (10 decimal) in an array
   * of the text separated by this character
   **/
  public String[] stringToVector(String strSQL, boolean suppressBlankLines) {
    byte tab[] = {10};
    String strTab = new String(tab);
    Vector<String> vector = new Vector<String>();
    if (strSQL == null) {
      return new String[0];
    }
    StringTokenizer tok = new StringTokenizer(strSQL, strTab);
    while (tok.hasMoreTokens()) {
      String sql = tok.nextToken();
      if (suppressBlankLines && sql.trim().equals("")) {
        continue;
      }
      vector.addElement(sql);
    }
    String[] strSqlVector = new String[vector.size()];
    vector.copyInto(strSqlVector);
    return strSqlVector;
  }

  static public String TransformaNombreColumna(String strColumn){
    return TransformaNombreColumna(strColumn, false);
  }

  static public String TransformaNombreFichero(String strFile){
    return TransformaNombreColumna(strFile, true);
  }

  static public String TransformaNombreColumna(String strName, boolean isFile){
    String strNombreTransformado = "";
    //    Character BarraBaja = new Character('_');
    String BarraBaja = "_";
    int intNumCaracteres = strName.length();
    boolean blnFueBarraBaja = false;
    for (int i=0; i<intNumCaracteres; i++){
      if (i==0) {
        if (isFile) {
          strNombreTransformado = new Character(Character.toUpperCase(strName.charAt(i))).toString();
        } else {
          strNombreTransformado = new Character(Character.toLowerCase(strName.charAt(i))).toString();
        }
      } else {
        //        if (new Character(strName.charAt(i)).compareTo(BarraBaja) == 0) blnFueBarraBaja = true;
        if (strName.substring(i,i+1).compareTo(BarraBaja) == 0) blnFueBarraBaja = true;
        else{
          if (blnFueBarraBaja){
            strNombreTransformado = strNombreTransformado + new Character(Character.toUpperCase(strName.charAt(i))).toString();
            blnFueBarraBaja = false;
          } else {
            if (isFile) {
              strNombreTransformado = strNombreTransformado + new Character(strName.charAt(i)).toString();
            } else {
              strNombreTransformado = strNombreTransformado + new Character(Character.toLowerCase(strName.charAt(i))).toString();
            }
          }
        }
      }
    }
    return(strNombreTransformado);
  }

  public void readProperties(String strFileProperties) {
    //  Read properties file.
    Properties properties = new Properties();
    try {
      log4j.info("strFileProperties: " + strFileProperties);
      properties.load(new FileInputStream(strFileProperties));
      javaDateFormat = properties.getProperty("dateFormat.java");
      log4j.info("javaDateFormat: " + javaDateFormat);
    } catch (IOException e) { 
      // catch possible io errors from readLine()
      System.out.println("Uh oh, got an IOException error!");
      e.printStackTrace();
    }
  }
  }


  /*
   *- names from Oracle to Java
   *- distintos type="preparedStatement, callableStatement, statement" 
   - treat statement
   *- distintos return="string, boolean, void, single(?), multiple" >
   *- inserts and updates with their return values
   *- parameters in sequences: sequence="nombreSecuencia"
   *-      <parameter type="Long" name="strExpediente" default="0" inOut="in, out, inout">
   No        valor
   *      </parameter>
   *- function names
   *- convert the SQL chain in various lines: strSql = strSql + " " the linebreak character is 0A (10 decimal)
   *- write commentaries to the functions and classes
   *- read type 
   *- read sequence in parameters
   - read connection parameters of getConnection
   *- return value when it is the value of a sequence
   - commentaries for the parameters
   *- do not write repeated parameters
   */
